CREATE PROCEDURE [dbo].[sp_cat_GetManufacturer]
@Manufacturer varchar(50) = NULL, @PKManufacturer int = NULL
AS
BEGIN
  SELECT PKManufacturer , Manufacturer FROM CT_Manufacturer
  WHERE PKManufacturer = COALESCE(@PKManufacturer , PKManufacturer)
  AND (Manufacturer LIKE '% '+@Manufacturer+'%' OR Manufacturer LIKE @Manufacturer+'%')
  ORDER BY 
    CASE 
      WHEN Manufacturer = @Manufacturer THEN 0
      WHEN Manufacturer LIKE @Manufacturer+'%' THEN 1
      ELSE 2
     END
     , Manufacturer;
END